/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.altius.dao.impl;

import cc.altius.dao.GeoZoneDao;
import cc.altius.model.GeographicalZone;
import cc.altius.model.MasterZone;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author sagar
 */
@Repository("GeoZoneDao")
public class GeoZoneDaoImpl implements GeoZoneDao {

    private SessionFactory sessionFactory;
    private HibernateTemplate hibernateTemplate;
    @Autowired
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
    Session session;
    Query query;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
        this.hibernateTemplate = new HibernateTemplate(sessionFactory);
    }

    @Override
    @Transactional
    public List<MasterZone> getMasterZoneList() {
        List<MasterZone> masterZone = this.hibernateTemplate.find("from MasterZone where geographicalZone IS NULL");
        return masterZone;
    }

    @Override
    @Transactional
    public int addGeoZone(GeographicalZone geoZone, String selectedMasterZoneIds) {
        session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
        hibernateTemplate.save(geoZone);
        if (!selectedMasterZoneIds.equals("")) {
            String arrselectedParameters[] = selectedMasterZoneIds.split(",");
            int intarray[] = new int[arrselectedParameters.length];
            for (int i = 0; i < arrselectedParameters.length; i++) {
                intarray[i] = Integer.parseInt(arrselectedParameters[i]);
                String hql = "update MasterZone set geographicalZone.geographicalZoneId=:geographicalZoneId"
                        + " where masterZoneId=:masterZoneId";
                query = session.createQuery(hql);
                query.setParameter("geographicalZoneId", geoZone.getGeographicalZoneId());
                query.setParameter("masterZoneId", intarray[i]);
                query.executeUpdate();
            }
        }
        return 1;
    }

//     @Override
//     @Transactional
//    public int addGeoZone(String geographicalZoneName, int cityId, String selectedMasterZoneIds) {
//         session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
//         String sql="INSERT INTO `healthspring`.`geographical_zone`(`GEOGRAPHICAL_ZONE_ID`,`GEOGRAPHICAL_ZONE_NAME`,`CITY_ID`) VALUES ( NULL,'"+geographicalZoneName+"','"+cityId+"') ";
//       
//      SimpleJdbcInsert test;
//      test=new SimpleJdbcInsert(dataSource).withTableName("geographical_zone").usingGeneratedKeyColumns("GEOGRAPHICAL_ZONE_ID");
//      Map<String,Object>params=new HashMap<String, Object>();
//      params.put("GEOGRAPHICAL_ZONE_NAME",geographicalZoneName);
//      params.put("CITY_ID",cityId);
//     int geoZoneId= test.executeAndReturnKey(params).intValue();
//        if (!selectedMasterZoneIds.equals("")) {
//            String arrselectedParameters[] = selectedMasterZoneIds.split(",");
//            int intarray[] = new int[arrselectedParameters.length];
//            for (int i = 0; i < arrselectedParameters.length; i++) {
//                intarray[i] = Integer.parseInt(arrselectedParameters[i]);
//                String hql = "update MasterZone set geographicalZone.geographicalZoneId=:geographicalZoneId"
//                        + " where masterZoneId=:masterZoneId";
//                query = session.createQuery(hql);
//                query.setParameter("geographicalZoneId",geoZoneId);
//                query.setParameter("masterZoneId", intarray[i]);
//                query.executeUpdate();
//            }
//        }
//        return 1;
//    }
    @Override
    @Transactional
    public List<Map<String, Object>> getGeoZoneList(int geographicalZoneId, int cityId) {
        String sql = "select geographical_zone.GEOGRAPHICAL_ZONE_ID, geographical_zone.GEOGRAPHICAL_ZONE_NAME, city.CITY_NAME, GROUP_CONCAT(master_zone.MASTER_ZONE_CODE ) as MASTER_ZONE_LIST from geographical_zone left join city on geographical_zone.CITY_ID =city.CITY_ID left join master_zone on geographical_zone.GEOGRAPHICAL_ZONE_ID =master_zone.GEOGRAPHICAL_ZONE_ID where master_zone.GEOGRAPHICAL_ZONE_ID is not null";

        if (geographicalZoneId != 0) {
            sql += " AND geographical_zone.GEOGRAPHICAL_ZONE_ID=" + geographicalZoneId;
        }

        if (cityId != 0) {
            sql += " AND city.CITY_ID=" + cityId;
        }

        sql += " group by geographical_zone.GEOGRAPHICAL_ZONE_ID ORDER BY city.`CITY_NAME`, geographical_zone.GEOGRAPHICAL_ZONE_NAME";

        //jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            System.out.println("" + e.getMessage());
            return null;
        }
    }

    @Override
    @Transactional
    public List<GeographicalZone> getGeoZoneList() {
        List<GeographicalZone> geoZone = this.hibernateTemplate.find("from GeographicalZone ");
        return geoZone;
    }

    @Override
    @Transactional
    public GeographicalZone getGeoZoneByGeoZoneId(int geographicalZoneId) {
        session = this.sessionFactory.getCurrentSession();
        Criteria criteria = session.createCriteria(GeographicalZone.class);
        criteria.add(Restrictions.eq("geographicalZoneId", geographicalZoneId));
        return (GeographicalZone) criteria.uniqueResult();
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getMasterZoneList(int geographicalZoneId) {
        String sql = "SELECT "
                + " master_zone.`MASTER_ZONE_ID` AS MASTER_ZONE_ID,master_zone.`MASTER_ZONE_CODE` AS MASTER_ZONE_CODE"
                + " FROM master_zone"
                + " LEFT JOIN geographical_zone ON geographical_zone.`GEOGRAPHICAL_ZONE_ID`=master_zone.`GEOGRAPHICAL_ZONE_ID`"
                + " WHERE geographical_zone.`GEOGRAPHICAL_ZONE_ID`=" + geographicalZoneId;
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            System.out.println("" + e.getMessage());
            return null;

        }
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getMasterZoneListByCityId(int cityId) {
        String sql = "SELECT "
                + " master_zone.`MASTER_ZONE_ID` AS MASTER_ZONE_ID,master_zone.`MASTER_ZONE_CODE` AS MASTER_ZONE_CODE"
                + " FROM master_zone"
                + " LEFT JOIN city ON city.`CITY_ID`=master_zone.`CITY_ID`"
                + " WHERE city.`CITY_ID`='" + cityId + "' AND  master_zone.GEOGRAPHICAL_ZONE_ID IS NULL";
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            System.out.println("" + e.getMessage());
            return null;

        }
    }

    @Override
    @Transactional
    public int updateGeoZone(GeographicalZone geoZone, String selectedMasterZoneIds) {
        session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
        hibernateTemplate.update(geoZone);
        String hql = "update MasterZone set geographicalZone.geographicalZoneId=NULL"
                + " where geographicalZone.geographicalZoneId=:geographicalZoneId";

        query = session.createQuery(hql);
        query.setParameter("geographicalZoneId", geoZone.getGeographicalZoneId());
        query.executeUpdate();
        if (!selectedMasterZoneIds.equals("")) {
            String arrselectedParameters[] = selectedMasterZoneIds.split(",");
            int intarray[] = new int[arrselectedParameters.length];
            for (int i = 0; i < arrselectedParameters.length; i++) {
                intarray[i] = Integer.parseInt(arrselectedParameters[i]);
                String hql1 = "update MasterZone set geographicalZone.geographicalZoneId=:geographicalZoneId"
                        + " where masterZoneId=:masterZoneId";
                query = session.createQuery(hql1);
                query.setParameter("geographicalZoneId", geoZone.getGeographicalZoneId());
                query.setParameter("masterZoneId", intarray[i]);
                query.executeUpdate();
            }
        }
        return 1;
    }
}
